﻿using System;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using System.Net;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using Microsoft.AnalysisServices.AdomdClient;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Security;
using Microsoft.SharePoint;

namespace KoPages.CONTROLTEMPLATES.userControls
{
    public partial class BI_Top10Shop : UserControl
    {
        #region Members
        #endregion

        #region App Settings
        static public string GetAppSetting(String mName)
        {
            String vStr = null;

            string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(strPath);
            XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
            foreach (XmlNode keynode in node.ChildNodes)
            {
                string keyStr = keynode.Attributes["key"].Value;
                if (keyStr == mName)
                {
                    vStr = keynode.Attributes["value"].Value;
                    break;
                }
            }

            if (String.IsNullOrEmpty(vStr))
                vStr = "";
            return vStr;
        }
        #endregion App Settings

        protected void Page_Load(object sender, EventArgs e)
        {
            //SPUser oUser = SPContext.Current.Web.CurrentUser;
            //SPSite oSite = SPContext.Current.Site;
            //SPWeb oWeb = SPContext.Current.Web;
            //bool bUsrFind = false;

            //foreach (SPGroup grp in oWeb.SiteGroups)
            //{

            //}

            //foreach (SPGroup oGroup in oUser.Groups)
            //{
            //}

            string connStr = GetAppSetting(@"BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            DataTable tbl = null;
            Exception err = new BI_SQL_Top10Shop().Get(ref tbl, Year, Month);
            StringBuilder sb = new StringBuilder();
            if (err == null)
            {
                err = new BI_SQL_Top10Shop().MakeHTML_001(ref sb, ref tbl);
                if (err == null)
                    oBody.Text = sb.ToString();
                else
                {
                    sb.Append(@"<p>Ошибка : " + err.Message + @"</p>");
                    oBody.Text = sb.ToString();
                }
            }
            else
            {
                sb.Append(@"<p>Ошибка : " + err.Message + @"</p>");
                oBody.Text = sb.ToString();
            }

        }

    }

    public class BI_SQL_Top10Shop
    {

        #region Common

        public static string MakeDT_Y_M(int Year, int Month)
        {
            return string.Format("{0:0000}{1:00}", Year, Month);
        }

        public string EncodeValue(Decimal val)
        {
            string ret = "";
            string str = val.ToString("C");
            if (str.Length > 5)
                ret = str.Substring(0, str.Length - 5);
            return ret;
        }

        #endregion

        public Exception Get(ref DataTable tbl, int parYear, int parMonth)
        {
            Exception ret = null;
            try
            {
                System.Net.WebClient wc = new System.Net.WebClient();
                wc.Headers["Cache-Control"] = "no-cache";
                wc.UseDefaultCredentials = false;
                wc.Credentials = new System.Net.NetworkCredential("anonymous", "janeDoe@contoso.com");
                wc.UseDefaultCredentials = false;
                byte[] data = wc.DownloadData(
@"http://sp.korablik.local:88/ws/BI/Top10.aspx?guid=" + new Guid().ToString());
                if (tbl == null)
                    tbl = new DataTable("tbl");
                if (data.Length > 0)
                {
                    using (System.IO.MemoryStream xmlStream = new System.IO.MemoryStream())
                    {
                        xmlStream.Write(data, 0, data.Length);
                        xmlStream.Position = 0;
                        tbl.ReadXml(xmlStream);

                        xmlStream.Position = 0;
                        StringBuilder sb = new StringBuilder();
                        foreach(byte bt in data)
                            sb.Append((char)bt);
                        string str = sb.ToString();
                        XDocument xErrors = XDocument.Parse(str);
                        var query = from g in xErrors.Descendants("Errors")
                        select new
                        {
                            errName = (string)g.Element("Err")
                        };
                        foreach (var node in query)
                        {
                            if (node.errName != null)
                                throw new Exception(node.errName);
                        }
                    }
                }
                else
                    throw new Exception(@"Web Service http://sp.korablik.local:88 не вернул данных");
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }
            return ret;
        }

        public Exception MakeHTML_001(ref StringBuilder sb, ref DataTable tbl)
        {

            #region Init

            Exception ret = null;

            Decimal min = 0;
            Decimal max = 0;
            foreach (DataRow r in tbl.Rows)
            {
                Decimal Deviation = 0;
                if (r["Deviation"] != null)
                    Deviation = Convert.ToDecimal(r["Deviation"]) * 100;
                if (Deviation < 0)
                {
                    if (min > Deviation)
                        min = Deviation;
                }
                else
                {
                    if (max < Deviation)
                        max = Deviation;
                }
            }

            if (min < 0)
                min = min * -1;

            if (max < min)
                max = min;
            if (min < max)
                min = max;

            DateTime dtNow = DateTime.Now;
            string dtStr = dtNow.Month.ToString() + @" / " + dtNow.Year.ToString();

            string clr = "#FFFFCC", clr1 = "#FFFFCC", clr2 = "#FFFFFF";
            string clr_last_col = "#003366";

            #endregion

            sb.Append("Магазины - Top 10 ; Сегодня : " + dtStr + @" .<br />" +
@"<TABLE width='600' style='BORDER-COLLAPSE: collapse;background-color: #FFFFFF; color: #FFFFFF; font-weight: bold;'
id=ctl00_GridView1 border=1 rules=all cellSpacing=0><TBODY>");

            sb.Append(
@"<TR style='background-color: #808080' >
<TH scope=col>&nbsp;&nbsp;Магазин</TH>
<TH scope=col>&nbsp;&nbsp;Оборот, Руб</TH>
<TH scope=col>&nbsp;&nbsp;План на месяц</TH>
<TH scope=col>&nbsp;&nbsp;Прогноз, руб</TH>
<TH scope=col>&nbsp;&nbsp;Отклонение</TH></TR>");

            int cnt = 0;
            string r_bottom = "";
            tbl.DefaultView.Sort = "Turnover";
            DataView dv = tbl.DefaultView;
            dv.Sort = "Turnover DESC";
            foreach (DataRowView r in dv)
            {
                cnt++;

                string name = "";
                if (r["Name"] != null)
                    name = r["Name"].ToString();
                Decimal Turnover = 0;
                if (r["Turnover"] != null)
                    Turnover = Convert.ToDecimal(r["Turnover"]);
                Decimal Plan = 0;
                if (r["Plan"] != null)
                    Plan = Convert.ToDecimal(r["Plan"]);
                Decimal Forecast = 0;
                if (r["Forecast"] != null)
                    Forecast = Convert.ToDecimal(r["Forecast"]);
                Decimal Deviation = 0;
                if (r["Deviation"] != null)
                    Deviation = Convert.ToDecimal(r["Deviation"]) * 100;

                if (cnt % 2 == 0) clr = clr1;
                else clr = clr2;
                if (cnt == 1) clr = "#AAAAAA";

                if (Deviation >= 0) clr_last_col = "#003366";
                else clr_last_col = "Maroon";

                string DeviationStr = String.Format("{0:0.00}", Deviation);
                DeviationStr = DeviationStr.Replace(",", ".");

                string str =
@"<TR style='background-color: " + clr + @"; font-size: small; font-weight: bold; color: #003366;'>
<TD align='left' >" + name + @"&nbsp;&nbsp;</TD>
<TD align='right'>" + SPEncode.HtmlEncode(EncodeValue(Turnover)) + @"&nbsp;&nbsp;</TD>
<TD align='right'>" + SPEncode.HtmlEncode(EncodeValue(Plan)) + @"&nbsp;&nbsp;</TD>
<TD align='right'>" + SPEncode.HtmlEncode(EncodeValue(Forecast)) + @"&nbsp;&nbsp;</TD>
<TD align='center' style='color:" + clr_last_col + @";'>" + SPEncode.HtmlEncode(DeviationStr) + @"&nbsp; % &nbsp;";

                str += @"</TD></TR>";

                /*
                if (Deviation < 0 && cnt != 1)
                {
                    str +=
                        //@"<TD align=left colspan='5'>
@"<table>
<tr>
<td align='left' width='";
                    int proc = (int)(Deviation * -1);
                    int i2 = (int)(proc / min * 100);
                    int i1 = 100 - i2;

                    str += i1.ToString() + @"px' style='background-color:#99FF99; color: #99FF99;' >|</td>
<td width='" + i2.ToString() + @"px' style='background-color:Red' ></td>
<td width='0px' style='background-color:Blue' ></td>
<td width='100px' style='background-color:#99FF99; color: #99FF99' >|</td>
</tr></table>
</TD></TR>";
                    //</table></TD>";
                }
                else if (Deviation > 0 && cnt != 1)
                {
                    str +=
                        //@"<TD align=left colspan='5' >
@"<table style='background-color: #DDDDDD'>
<tr style='background-color: #DDDDDD'>
<td align='left' width='";
                    int proc = (int)(Deviation);
                    int i1 = (int)(proc / max * 100);
                    int i2 = 100 - i1;

                    str += @"100px' style='background-color:#99FF99; color: #99FF99;' >|</td>
<td width='0px' style='background-color:Red' ></td>
<td width='" + i1.ToString() + @"px' style='background-color:Blue' ></td>
<td width='" + i2.ToString() + @"px' style='background-color:#99FF99; color: #99FF99;' >|</td>
</tr></table>
</TD></TR>";
                    //</table></TD>";
                }
                else if (cnt == 1)
                {
                    str += @"</TD></TR>";
                }
                else if (cnt != 1)
                {
                    str +=
                        //@"<TD align=left colspan='5'>
@"<table>
<tr>
<td align='left' width='";
                    int proc = (int)(Deviation);
                    int i1 = (int)(proc / max * 100);
                    int i2 = 100 - i1;

                    str += @"100px' style='background-color:#99FF99; color: #99FF99;' >|</td>
<td width='0px' style='background-color:Red' ></td>
<td width='0px' style='background-color:Blue' ></td>
<td width='100px' style='background-color:#99FF99; color: #99FF99;' >|</td>
</tr></table>
</TD></TR>";
                    //</table></TD>";
                }
                */

                if (r["Name"].ToString() == "Все")
                    r_bottom = str;
                else
                    sb.Append(str);
            }

            sb.Append(r_bottom);
            sb.Append(@"</TBODY></TABLE>");

            return ret;
        }

    }

}
